﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ActivitiesControl.Domain.DAL;
using ActivitiesControl.Domain.Model;
using System.Data.SqlClient;
using System.Data;
using ActivitiesControl.Domain.Utils;
using System.Diagnostics;
using ActivitiesControl.Domain.Model.Entities;
using ActivitiesControl.Domain.Model.VOs;

namespace ActivitiesControl.Repository.DAL.SqlServer
{
    internal class SqlServerActivityRepository
        : SqlServerBaseRepository<Activity>, IActivityRepository
    {
		public IEnumerable<string> GetActivityTypes(int customerId)
		{
			try
			{
				Connection.Open();
				SqlCommand cmd = Connection.CreateCommand();
				cmd.CommandText = string.Format("select a.activityType from Activities a inner join Projects p on a.projectId = p.projectId where p.customerId = {0} group by a.activityType", customerId);
				SqlDataAdapter adapter = new SqlDataAdapter(cmd);
				DataTable result = new DataTable();
				adapter.Fill(result);

				foreach (DataRow row in result.Rows)
					yield return row["activityType"].ToString();
			}
			finally
			{
				Connection.Close();
			}
		}

        public IEnumerable<Activity> GetActivitiesByResourceMonth(int resourceId, int month)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();
                cmd.CommandText = "select * from Activities where resourceId = @resourceId and MONTH(date) = @month";
                cmd.Parameters.AddWithValue("month", month);
                cmd.Parameters.AddWithValue("resourceId", resourceId);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable result = new DataTable();
                adapter.Fill(result);

                foreach (DataRow row in result.Rows)
                    yield return Row2Object(row);
            }
            finally
            {
                Connection.Close();
            }

        }

        public void Add(Activity entity)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();

                cmd.CommandText = "insert into Activities(projectId, resourceId, date, description, effortHours, activityType) values(@projectId, @resourceId, @date, @description, @effortHours, @activityType)";
                cmd.Parameters.AddWithValue("projectId", entity.ProjectId);
                cmd.Parameters.AddWithValue("resourceId", entity.ResourceId);
                cmd.Parameters.AddWithValue("date", entity.Date.DateTime);
                cmd.Parameters.AddWithValue("description", entity.Description);
                cmd.Parameters.AddWithValue("effortHours", entity.EffortHours);
                cmd.Parameters.AddWithValue("activityType", entity.ActivityType);

                cmd.ExecuteNonQuery();
            }
            finally
            {
                Connection.Close();
            }
        }

        public void Update(Activity entity)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();

                cmd.CommandText = "update Activities set projectId = @projectId, resourceId = @resourceId, date = @date, description = @description, effortHours= @effortHours, activityType= @activityType where activityId = @activityId";
                cmd.Parameters.AddWithValue("activityId", entity.Id);
                cmd.Parameters.AddWithValue("projectId", entity.ProjectId);
                cmd.Parameters.AddWithValue("resourceId", entity.ResourceId);
                cmd.Parameters.AddWithValue("date", entity.Date.DateTime);
                cmd.Parameters.AddWithValue("description", entity.Description);
                cmd.Parameters.AddWithValue("effortHours", entity.EffortHours);
                cmd.Parameters.AddWithValue("activityType", entity.ActivityType);

                cmd.ExecuteNonQuery();
            }
            finally
            {
                Connection.Close();
            }
        }

        public void Remove(Activity entity)
        {
			RemoveById(entity.Id);
        }

		public void RemoveById(int id)
		{
			try
			{
				Connection.Open();
				SqlCommand cmd = Connection.CreateCommand();
				cmd.CommandText = "delete from Activities where activityId =  @activityId";
				cmd.Parameters.AddWithValue("activityId", id);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				Connection.Close();
			}
		}

        public Activity GetById(int id)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();
                cmd.CommandText = "select * from Activities where activityId = @activityId";
                cmd.Parameters.AddWithValue("activityId", id);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable result = new DataTable();
                adapter.Fill(result);
                if (result != null && result.Rows != null && result.Rows.Count > 0)
                    return Row2Object(result.Rows[0]);

                return null;
            }
            finally
            {
                Connection.Close();
            }
        }

        public IEnumerable<Activity> GetAll()
        {
            throw new NotImplementedException();
        }

        protected override Activity Row2Object(DataRow row)
        {
            return new Activity
            {
                Date = new Date(Convert.ToDateTime(row["date"])),
                Description = row["description"].ToString(),
                EffortHours = Convert.ToInt32(row["effortHours"]),
                Id = Convert.ToInt32(row["activityId"]),
                //IsChargeable = Convert.ToBoolean(row["isChargeable"]),
                ActivityType = row["activityType"].ToString(),
                ProjectId = Convert.ToInt32(row["projectId"]),
                ResourceId = Convert.ToInt32(row["resourceId"])
            };
        }

        public IEnumerable<Activity> GetActivitiesByMonth(int month)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Activity> GetActivitiesByResourcePeriod(int resourceId, Date begin, Date end)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();
                cmd.CommandText = "select * from Activities where date >= @begin and date <= @end and resourceId = @resourceId";
                cmd.Parameters.AddWithValue("begin", begin.DateTime);
                cmd.Parameters.AddWithValue("end", end.DateTime);
                cmd.Parameters.AddWithValue("resourceId", resourceId);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable result = new DataTable();
                adapter.Fill(result);

                foreach (DataRow row in result.Rows)
                    yield return Row2Object(row);
            }
            finally
            {
                Connection.Close();
            }
        }

        public IEnumerable<Activity> GetActivities(int resourceId, Date begin, Date end, int customerId, int projectId)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = Connection.CreateCommand();

				StringBuilder sql = new StringBuilder();

				sql.AppendFormat(" select a.*  ");
				sql.AppendFormat(" from Activities a  ");
				sql.AppendFormat(" 	inner join Projects p	on a.projectId = p.projectId ");
				sql.AppendFormat(" 	inner join Customers c	on p.customerId = c.customerId ");
				sql.AppendFormat(" where a.date between @begin and @end  ");
				sql.AppendFormat(" 	and a.resourceId = @resourceId  ");
				sql.AppendFormat(" 	and c.customerId = @customerId  ");
				sql.AppendFormat(" 	and (a.projectId = @projectId or @projectId = -999) ");

				cmd.CommandText = sql.ToString();
                cmd.Parameters.AddWithValue("begin", begin.DateTime);
                cmd.Parameters.AddWithValue("end", end.DateTime);
				cmd.Parameters.AddWithValue("customerId", customerId);
                cmd.Parameters.AddWithValue("resourceId", resourceId);
				cmd.Parameters.AddWithValue("projectId", projectId);

				Debug.WriteLine(sql.ToString());
                
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable result = new DataTable();
                adapter.Fill(result);

                foreach (DataRow row in result.Rows)
                    yield return Row2Object(row);
            }
            finally
            {
                Connection.Close();
            }
        }
	}
}
